import requests
import pymysql
import datetime
from bs4 import BeautifulSoup


headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) "
                  "Chrome/80.0.3987.149 Safari/537.36",
}


def getChina500(code, year):
    url = 'https://www.fortunechina.com/china500/'+str(code)+'/'+str(year)
    r = requests.get(url=url, headers=headers)
    r.encoding = 'UTF_8'
    soup = BeautifulSoup(r.text, "html.parser")
    title = soup.title.text
    if title != '没有查找到公司资料 - 中国500强 - 财富中文网':
        tds = soup.find_all('td')
        ps = soup.find_all('p')
        names = soup.find_all('div', class_='comp-name')
        sps = str(names[0]).replace('<div class="comp-name">', '').replace('</div>', '').split('<br/>')
        em1 = soup.find_all('em', class_='r1')[0].text
        em2 = soup.find_all('em', class_='r2')[0].text
        td1 = tds[4].text
        td2 = tds[5].text
        td3 = tds[7].text
        td4 = tds[8].text
        td5 = tds[10].text
        td6 = tds[11].text
        td7 = tds[13].text
        td8 = tds[14].text
        td9 = tds[16].text
        td10 = tds[17].text
        td11 = tds[19].text
        td12 = tds[20].text
        td13 = tds[22].text
        td14 = tds[23].text
        td15 = tds[25].text
        td16 = tds[26].text
        td17 = tds[28].text
        td18 = tds[29].text
        p1 = ps[1].text
        p2 = ps[2].text
        p3 = ps[4].text
        p4 = ps[6].find('a').text
        p5 = ps[5].text
        p6 = ps[8].text.split('  - 行业横向比较')[0].strip()
        name1 = sps[0]
        name2 = sps[1]
        save_sql = "insert into china_500 (`c_name`, `e_name`, `p_year`, `n_order`," \
                   " `l_order`, `address`, `ceo`, `p_number`, `website`, `stock_code`, `industry`, `income`, " \
                   "`income_ld`, `profit`, `profit_ld`, `assets`, `assets_ld`, " \
                   "`market`, `market_ld`, `equity`, `equity_ld`, `proportion`, " \
                   "`proportion_ld`, `interest`, `interest_ld`, `net_assets_rate`, " \
                   "`net_assets_rate_ld`, `assets_rate`, `assets_rate_ld`) values (" \
                   "\""+str(name1).strip()+"\", \""+str(name2).strip()+"\", \""+str(year)+"\", \""+str(em1).strip()+"\", \""+str(em2).strip()+"\", \""+str(p1).strip()+"\", \""+str(p2).strip()+"\", " \
                   "\""+str(p3).strip()+"\", \""+str(p4).strip()+"\", \""+str(p5).strip()+"\", \""+str(p6).strip()+"\", \""+str(td1).strip()+"\", \""+str(td2).strip()+"\", \""+str(td3).strip()+"\", \""+str(td4).strip()+"\", \""+str(td5).strip()+"\", " \
                   "\""+str(td6).strip()+"\", \""+str(td7).strip()+"\", \""+str(td8).strip()+"\", \""+str(td9).strip()+"\", \""+str(td10).strip()+"\", \""+str(td11).strip()+"\", \""+str(td12).strip()+"\", " \
                   "\""+str(td13).strip()+"\", \""+str(td14).strip()+"\", \""+str(td15).strip()+"\", \""+str(td16).strip()+"\", \""+str(td17).strip()+"\", \""+str(td18).strip()+"\")"
        res = saveSql(save_sql)
        print(str(code) + "---" + str(year) + "---" + name1 + "---" + res)


def loop():
    del_sql = "delete from china_500 where p_year = '" + str(datetime.datetime.now().year) + "'"
    saveSql(del_sql)
    for i in range(1, 1001):
        for z in range(datetime.datetime.now().year, datetime.datetime.now().year + 1):
            getChina500(i, z)


def saveSql(sql):
    con = None
    cur = None
    result = '执行成功'
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            cur = con.cursor()
            cur.execute(sql)
            con.commit()
        else:
            print("链接失败")
    except Exception as e:
        print(e)
        print('开始重试')
        saveSql(sql)
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    return result


loop()